package com.tcfvic.server.upload;

import java.io.BufferedOutputStream;
import java.io.DataOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import jxl.Cell;
import jxl.DateCell;
import jxl.NumberCell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.FileItemFactory;
import org.apache.commons.fileupload.ProgressListener;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.apache.log4j.Logger;
import org.datanucleus.util.StringUtils;

import com.tcfvic.client.LogLevel;
import com.tcfvic.client.pojos.Address;
import com.tcfvic.client.pojos.Course;
import com.tcfvic.client.pojos.Employmentdetails;
import com.tcfvic.client.pojos.EmploymentdetailsId;
import com.tcfvic.client.pojos.Expense;
import com.tcfvic.client.pojos.ExpenseId;
import com.tcfvic.client.pojos.Fundingsource;
import com.tcfvic.client.pojos.Logs;
import com.tcfvic.client.pojos.Person;
import com.tcfvic.client.pojos.Personaddress;
import com.tcfvic.client.pojos.Personcourse;
import com.tcfvic.client.pojos.PersoncourseId;
import com.tcfvic.client.pojos.Personcoursefundingsource;
import com.tcfvic.client.pojos.Rto;
import com.tcfvic.client.pojos.Workercompany;
import com.tcfvic.server.PersonServiceImpl;

public class ExcelAllDataFormat extends HttpServlet implements IExcelFormat {
	private static final long serialVersionUID = 5567237221724423872L;

	private static final Logger logger = Logger
			.getLogger(ExcelAllDataFormat.class);

	private List<String> errorList = new ArrayList<String>();
	private int numberImported = 0;

	private Map<Integer, Person> persons;
	private Map<Integer, Employmentdetails> personCompany;
	private Map<String, Course> courses;
	private Map<String, Fundingsource> fundingSources;
	private Map<String, Workercompany> companies;
	private PersonServiceImpl personService = new PersonServiceImpl();
	private Map<String, Rto> rtos;

	public ExcelAllDataFormat() {
		writeLog("Uploading an ALL DATA file format.", LogLevel.INFO);
		personService = new PersonServiceImpl();
		initialise();
	}

	public static void main(String... args) {
		ExcelAllDataFormat f = new ExcelAllDataFormat();
		f.uploadFile(new File("C:/temp/FINAL - All Data Master.xls"));
	}

	@Override
	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		response.setContentType("text/plain");
		logger
				.info("Inside All Data File Upload Servlet...............>>>>>>>>>>");
		personService = new PersonServiceImpl();
		initialise();
		try {
			getFileItem(request);
		} catch (Exception e) {
			writeLog(e.getMessage(), LogLevel.ERROR);
		}
	}

	@SuppressWarnings("unchecked")
	private FileItem getFileItem(final HttpServletRequest request) {
		FileItemFactory factory = new DiskFileItemFactory();
		ServletFileUpload upload = new ServletFileUpload(factory);
		// Create a progress listener, if want to use
		ProgressListener progressListener = new ProgressListener() {
			private long megaBytes = -1;
			int i = -1;

			public void update(long pBytesRead, long pContentLength, int pItems) {
				if (i != pItems) {
					i = pItems;
				}
				long mBytes = pBytesRead / 10000;
				if (megaBytes == mBytes) {
					return;
				}
				megaBytes = mBytes;
			}
		};
		upload.setProgressListener(progressListener);

		try {
			List items = upload.parseRequest(request);
			Iterator it = items.iterator();
			while (it.hasNext()) {
				FileItem item = (FileItem) it.next();
				if (!item.isFormField()) {
					this.processUpload(item);
				}
			}
		} catch (Exception e) {
			writeLog(e.getMessage(), LogLevel.ERROR);
			return null;
		}
		return null;
	}

	private void writeLog(String text, LogLevel level) {
		switch (level) {
		case INFO:
			logger.info(text);
			break;
		case WARN:
			logger.warn(text);
			break;
		case ERROR:
			logger.error(text);
			break;
		default:
			logger.error(text);
			break;
		}
		try {
			personService.writeLog(new Logs(new Date(), text));
		} catch (Exception e) {
			logger.error("Error writing log to database.", e);
		}
	}

	private void processUpload(FileItem uploadItem) {
		try {
			String uploadDir = getServletContext().getRealPath("/")
					+ "tcfuaUplaod/";
			File file = new File(uploadDir);
			file.mkdirs();

			InputStream is = uploadItem.getInputStream();
			String filename = String.valueOf(System.currentTimeMillis())
					+ ".xls";
			File uploadedFile = new File(uploadDir + "/" + filename);
			DataOutputStream out = null;
			out = new DataOutputStream(new BufferedOutputStream(
					new FileOutputStream(uploadedFile)));
			while (true) {
				int value = is.read();
				out.write(value);
				if (value < 0) {
					break;
				}
			}
			out.close();
			uploadFile(uploadedFile);

		} catch (IOException e) {
			writeLog(e.getMessage(), LogLevel.ERROR);
		}
	}

	public void initialise() {
		List<Course> result = personService.getAllTraining();
		courses = new HashMap<String, Course>();
		for (Course course : result) {
			courses.put(course.getName(), course);
		}

		List<Person> personResultList = personService.getAllPersonDetails();
		persons = new HashMap<Integer, Person>();
		for (Person person : personResultList) {
			persons.put(person.getId(), person);
		}

		List<Rto> rtoResult = personService.getAllRTOs();
		rtos = new HashMap<String, Rto>();
		for (Rto rto : rtoResult) {
			rtos.put(rto.getName() + rto.getCampus(), rto);
		}

		List<Workercompany> wcResultList = personService.getAllCompanies();
		companies = new HashMap<String, Workercompany>();
		for (Workercompany company : wcResultList) {
			companies.put(company.getName() + company.getSite(), company);
		}

		List<Fundingsource> allFundingSources = personService
				.getAllFundingSources();
		fundingSources = new HashMap<String, Fundingsource>();
		for (Fundingsource fs : allFundingSources) {
			fundingSources.put(fs.getSourceName(), fs);
		}

		List<Employmentdetails> allEmpDetails = personService
				.getAllPersonEmploymentDetails();
		personCompany = new HashMap<Integer, Employmentdetails>();
		for (Employmentdetails ed : allEmpDetails) {
			personCompany.put(ed.getId().getPersonId()
					+ ed.getId().getWorkerCompanyId(), ed);
		}
	}

	public void initialiseRTOs() {
		List<Rto> rtoResult = personService.getAllRTOs();
		rtos = new HashMap<String, Rto>();
		for (Rto rto : rtoResult) {
			rtos.put(rto.getName() + rto.getCampus(), rto);
		}
	}
	public void initialiseCourse() {
		List<Course> result = personService.getAllTraining();
		courses = new HashMap<String, Course>();
		for (Course course : result) {
			courses.put(course.getName(), course);
		}
	}

	public void initialiseFundingSources() {
		List<Fundingsource> allFundingSources = personService
				.getAllFundingSources();
		fundingSources = new HashMap<String, Fundingsource>();
		for (Fundingsource fs : allFundingSources) {
			fundingSources.put(fs.getSourceName(), fs);
		}
	}
	public void initialiseCompanies() {
		List<Workercompany> wcResultList = personService.getAllCompanies();
		companies = new HashMap<String, Workercompany>();
		for (Workercompany company : wcResultList) {
			companies.put(company.getName() + company.getSite(), company);
		}
	}

	public void uploadFile(File file) {
		writeLog("In ExcelTCVVicFormat.uploadFile", LogLevel.INFO);

		try {
			Workbook workbook = createWorkbook(file);
			if (workbook.getSheets() != null) {
				processSheets(workbook);
			}
		} catch (Exception e) {
			writeLog(e.getMessage(), LogLevel.ERROR);
		}
	}

	public void processSheets(Workbook workbook) throws Exception {
		Sheet sheet = workbook.getSheet(0);
		int i = 1;
		while (true) {
			i++;
			Person person = null;
			if (sheet.getCell("D" + i) != null
					&& !sheet.getCell("D" + i).getContents().isEmpty()) {
				try {
					int j = 0;
					long time = System.currentTimeMillis();
					String personNameAlreadyInDatabase = getNameIfPersonExists(sheet
							.getRow(i - 1));
					if (personNameAlreadyInDatabase != null) {
						String msg = "Person not imported as they already exist - "
								+ personNameAlreadyInDatabase;
						errorList.add(msg);
						writeLog(msg, LogLevel.WARN);
						continue;
					}
					System.out.println(++j + " "
							+ (System.currentTimeMillis() - time));
					time = System.currentTimeMillis();
					try {
						person = processPersonRecord(sheet.getRow(i - 1));
					} catch (Exception e) {
						String msg = "Sheet not processed due to: "
								+ e.getMessage();
						errorList.add(msg);
						writeLog(msg, LogLevel.WARN);
						continue;
					}
					System.out.println(++j + " "
							+ (System.currentTimeMillis() - time));
					time = System.currentTimeMillis();

					person.setEmploymentdetailses(processCompany(sheet
							.getRow(i - 1), person));
					System.out.println(++j + " "
							+ (System.currentTimeMillis() - time));
					time = System.currentTimeMillis();
					person.setPersoncourses(processPersonCourses(sheet
							.getRow(i - 1), person));
					processExpense(sheet.getRow(i - 1), person);
					System.out.println(++j + " "
							+ (System.currentTimeMillis() - time));
					time = System.currentTimeMillis();

					personService.savePersonDetails(person);
					System.out.println(++j + " "
							+ (System.currentTimeMillis() - time));
					time = System.currentTimeMillis();
					numberImported++;
				} catch (Exception e) {
					writeLog("An error occured importing data for "
							+ person.getFirstname() + ", "
							+ person.getSurname() + ": " + e.getMessage(),
							LogLevel.ERROR);
				}
			} else {
				break;
			}
			Thread.yield();
			Thread.sleep(100);
		}
	}

	private Set<Employmentdetails> processCompany(Cell[] row, Person person)
			throws Exception {
		// Company details
		String employeeNo = row[19].getContents();
		String companyName = row[0].getContents();
		String companySite = row[1].getContents();
		Cell startDate = getCell(row, 20);
		Cell endDate = getCell(row, 21);
		String position = row[22].getContents();
		String status = row[23].getContents();
		String TCFSector = row[24].getContents();

		if (companySite == null || companySite.trim().length() == 0) {
			return null;
		}
		if (!companies.containsKey(companyName + companySite)) {
			Workercompany company = new Workercompany();
			// company.setName(PACIFIC_BRANDS_NAME);
			company.setName(companyName);
			company.setSite(companySite);
			personService.saveCompanyDetails(company);
			initialiseCompanies();
		}

		String companySiteName = companySite;
		if (companySiteName == null) {
			companySiteName = "";
		}
		Set<Employmentdetails> employmentdetailsSet = new HashSet<Employmentdetails>();
		Workercompany company = companies.get(companyName + companySiteName);
		Employmentdetails empDetails = new Employmentdetails();
		empDetails.setId(new EmploymentdetailsId(company.getId(), person
				.getId()));
		if (StringUtils.isEmpty(employeeNo)) {
			writeLog("While importing from file, " + person.getFirstname()
					+ " " + person.getSurname()
					+ " has no employee number registerd", LogLevel.WARN);
			errorList.add(person.getFirstname() + " " + person.getSurname()
					+ " has no employee number registerd");
		}
		empDetails.setEmployeenumber(employeeNo);
		if (startDate instanceof DateCell) {
			empDetails.setDatestarted(((DateCell) startDate).getDate());
		}
		if (endDate instanceof DateCell) {
			empDetails.setDateleaving(((DateCell) endDate).getDate());
		}
		empDetails.setPosition(position);
		empDetails.setStatus(status);
		empDetails.setSector(TCFSector);
		employmentdetailsSet.add(empDetails);
		person.setEmploymentdetailses(employmentdetailsSet);

		return employmentdetailsSet;
	}

	private String getStringFromCell(Cell[] row, int col) {
		try {
			return row[col].getContents().trim();
		} catch (Exception e) {
			return "";
		}
	}

	private Cell getCell(Cell[] row, int col) {
		try {
			return row[col];
		} catch (Exception e) {
			return null;
		}
	}

	private Set<Personcourse> processPersonCourses(Cell[] row, Person person)
			throws Exception {
		Set<Personcourse> pcSet = new HashSet<Personcourse>();
		int startCol = 24;
		int courseCount = 0;
		while (true) {
			Personcourse pc = new Personcourse();
			if (row.length <= (startCol + 1) || row[startCol + 1] == null
					|| row[startCol + 1].getContents().isEmpty()
					|| courseCount == 10) {
				break;
			}
			String courseName = getStringFromCell(row, ++startCol);
			String courseType = getStringFromCell(row, ++startCol);
			String rtoName = getStringFromCell(row, ++startCol);
			String rtoCampus = getStringFromCell(row, ++startCol);
			Cell startDate = getCell(row, ++startCol);
			Cell endDate = getCell(row, ++startCol);
			Cell cost = getCell(row, ++startCol);
			Cell fundingSource = getCell(row, ++startCol);
			// String cost = row[startCol++].getContents().trim();
			// String pacBrandsPayment = row[startCol++].getContents().trim();
			// String tcfSAPPayment = row[startCol++].getContents().trim();
			// String SAP_PPP = row[startCol++].getContents().trim();
			// String pbgDays = row[startCol++].getContents().trim();
			// String pbgHours = row[startCol++].getContents().trim();

			Course course = processCourse(courseName, courseType);

			try {
				double costValue = 0;
				if (cost instanceof NumberCell) {
					costValue = ((NumberCell) cost).getValue();
				}
				addCourseExpense(startCol, person, course, fundingSource
						.getContents(), costValue);
			} catch (Exception e) {
				writeLog("Couldn't upload course cost/FS: " + e, LogLevel.ERROR);
			}
			// Fundingsource fs = fundingSources.get();

			pc.setCompleted("true");
			if (rtoName != null && rtoName.trim().length() > 0) {
				pc.setRto(processRTO(rtoName, rtoCampus));
			}
			pc.setCourse(course);
			if (startDate != null) {
				try {
					pc.setTrainingdate(((DateCell) startDate).getDate());
				} catch (Exception e) {
					writeLog("  Ok to ignore: " + person.getFirstname() + " "
							+ person.getSurname()
							+ ": Training start date could not be extracted - "
							+ startDate.getContents(), LogLevel.INFO);
				}
			}
			if (endDate != null) {
				try {
					pc.setFinishDate(((DateCell) endDate).getDate());
				} catch (Exception e) {
					logger
							.info("  Ok to ignore: "
									+ person.getFirstname()
									+ " "
									+ person.getSurname()
									+ ": Training finish date could not be extracted - "
									+ endDate.getContents());
				}
			}
			// Create PCFS list
			Set<Personcoursefundingsource> pcfsSet = new HashSet<Personcoursefundingsource>();

			pc.setPersoncoursefundingsources(pcfsSet);
			pc.setId(new PersoncourseId(0, person.getId(), course.getId()));
			pcSet.add(pc);
			courseCount++;
		}
		return pcSet;
	}

	private void addCourseExpense(int expIdShifter, Person person,
			Course course, String fundingSource, double cost) throws Exception {
		if (!fundingSources.containsKey(fundingSource)) {
			Fundingsource fs = new Fundingsource();
			// company.setName(PACIFIC_BRANDS_NAME);
			fs.setSourceName(fundingSource);
			personService.saveFundingSourceDetails(fs);
			initialiseFundingSources();
		}

		Expense expense = new Expense();
		// 1000 used here because a number has to be specified. When we process
		// the expenses later we won't get to 1000, so we basically need
		// a unique number (which will be 1000 + the current col number)
		expense.setId(new ExpenseId(1000 + expIdShifter, person.getId()));
		expense.setPerson(person);
		expense.setFundingsource(fundingSources.get(fundingSource));
		expense.setAmount(cost);
		expense.setRelatedCourse(String.valueOf(courses.get(course.getName())
				.getId()));
		expense.setDescription("Course fees");
		personService.saveExpense(expense);
	}

	private Rto processRTO(String name, String campus) throws Exception {
		if (rtos.containsKey(name + campus)) {
			return rtos.get(name + campus);
		} else {
			Rto rto = new Rto();
			rto.setName(name);
			rto.setCampus(campus);
			personService.saveRTO(rto);
			initialiseRTOs();
			return rtos.get(name + campus);
		}
	}

	private void processExpense(Cell[] row, Person person) throws Exception {

		List<Expense> expenses = new ArrayList<Expense>();
		int startCol = 105;
		int counter = 1;
		while (true) {
			if (startCol >= row.length || row[startCol] == null
					|| row[startCol].getContents() == null
					|| row[startCol].getContents().isEmpty()) {
				break;
			}
			String desc = getStringFromCell(row, startCol++);
			Cell amount = getCell(row, startCol++);
			String fundingSource = getStringFromCell(row, startCol++);
			Expense expense = new Expense();
			expense.setId(new ExpenseId(counter++, person.getId()));
			expense.setDescription(desc);
			try {
				expense.setAmount(((NumberCell) amount).getValue());
			} catch (Exception e) {
				writeLog(e.getMessage(), LogLevel.ERROR);
			}
			try {
				if (fundingSource != null && fundingSource.trim().length() > 0) {
					expense.setFundingsource(getFundingSource(fundingSource,
							null));
				}
			} catch (Exception e) {
			}
			expense.setPerson(person);
			expenses.add(expense);
		}
		personService.saveExpenses(expenses, false);
	}

	private Fundingsource getFundingSource(String sourceName,
			String businessName) {
		for (Fundingsource fsCheck : fundingSources.values()) {
			if (/*
				 * fsCheck.getBusinessName().equals(businessName) &&
				 */fsCheck.getSourceName().equals(sourceName)) {
				return fsCheck;
			}
		}
		return null;
	}

	private Course processCourse(String courseName, String courseType)
			throws Exception {
		// get course or create course
		Course course = courses.get(courseName);
		if (course == null) {
			course = new Course();
			course.setName(courseName.trim());
			course.setCourseType(courseType.trim());
			personService.saveTrainingDetails(course);
			initialiseCourse();
			course = courses.get(courseName);
		}
		return course;
	}

	private String getNameIfPersonExists(Cell[] row) {
		String surname = fullCamelCase(row[3].getContents());
		String firstname = fullCamelCase(row[2].getContents());
		String site = fullCamelCase(row[1].getContents()).trim();

		for (Person person : persons.values()) {
			if (person.getSurname().equals(surname)
					&& person.getFirstname().equals(firstname)) {
				// Employmentdetails ed = personCompany.get(person.getId());
				// if (ed != null && ed.getWorkercompany() != null) {
				// Workercompany wc = companies.get(ed.getWorkercompany()
				// .getId());
				// if (wc.getSite().equals(site)) {
				return "Firstname: " + person.getFirstname() + ", Surname: "
						+ person.getSurname() + " at site: " + site;
				// }
				// }
			}
		}
		return null;
	}

	private static String capitalize(String s) {
		if (s.length() == 0)
			return s;
		return s.substring(0, 1).toUpperCase() + s.substring(1).toLowerCase();
	}

	private String fullCamelCase(String camelMe) {
		StringBuffer sb = new StringBuffer();
		String[] words = camelMe.split("\\s");
		for (String s : words) {
			sb.append(capitalize(s));
		}
		return sb.toString();
	}

	private Person processPersonRecord(Cell[] row) throws Exception {
		String cellSurname = fullCamelCase(row[3].getContents().trim());
		String cellFirstname = fullCamelCase(row[2].getContents().trim());
		// String cellTitle = fullCamelCase(row[2].getContents().trim());
		String cellStreetaddress = row[6].getContents().trim();
		String cellSuburb = row[7].getContents().trim();
		String cellState = row[8].getContents().trim();
		String cellPostcode = row[9].getContents().trim();
		String cellPhone = row[10].getContents().trim();
		String cellMobile = row[11].getContents().trim();
		String cellEmail = row[12].getContents().trim();
		Cell birthDate = row[4];
		String gender = row[5].getContents().trim();
		String cellUnionMemberNo = row[13].getContents();
		String cellCountryOrigin = row[14].getContents();
		Cell cellDateMigration = row[15];
		String cellFirstLang = row[16].getContents();
		String cellHighestLevel = row[17].getContents();
		String cellCountryObtained = row[18].getContents();
		if (cellFirstname == null || cellFirstname.isEmpty()) {
			throw new Exception("Row is missing given name");
		}

		if (cellSurname == null || cellSurname.isEmpty()) {
			throw new Exception("Row is missing surname");
		}

		writeLog("Importing " + cellFirstname + " " + cellSurname,
				LogLevel.INFO);

		Person person = new Person();

		Address address = new Address();
		if (cellSurname != null) {
			person.setSurname(cellSurname);
		}
		if (cellFirstname != null) {
			person.setFirstname(cellFirstname);
		}
		// if (cellTitle != null) {
		// person.setTitle(cellTitle);
		// }
		if (cellStreetaddress != null) {
			address.setStreet(cellStreetaddress);
		}
		if (cellSuburb != null) {
			address.setSuburb(cellSuburb);
		}
		if (cellState != null) {
			address.setState(cellState);
		}
		if (cellPostcode != null) {
			try {
				address.setPostcode(new Integer(cellPostcode));
			} catch (Exception e) {
			}
		}
		if (cellPhone != null) {
			person.setLandline(cellPhone);
		}
		if (cellMobile != null) {
			person.setMobile(cellMobile);
		}
		if (cellEmail != null) {
			person.setEmail(cellEmail);
		}
		if (gender != null) {
			person.setGender(gender);
		}
		if (cellUnionMemberNo != null) {
			person.setUnionMemberNo(cellUnionMemberNo);
		}
		try {
			person.setDateofbirth(((DateCell) birthDate).getDate());
		} catch (Exception e) {
			writeLog(
					"Birth date could not be determined due to invalid format.",
					LogLevel.ERROR);
		}
		if (cellCountryOrigin != null) {
			person.setCountryOfOrigin(cellCountryOrigin);
		}
		if (cellDateMigration != null) {
			try {
				person.setDateofmigration(((DateCell) cellDateMigration)
						.getDate());
			} catch (Exception e) {
			}
		}
		if (cellFirstLang != null) {
			person.setFirstLanguage(cellFirstLang);
		}
		if (cellHighestLevel != null) {
			person.setFormalEdHighLevel(cellHighestLevel);
		}
		if (cellCountryObtained != null) {
			person.setFormalEdCountry(cellCountryObtained);
		}

		Personaddress personAddress = new Personaddress();
		personAddress.setAddress(address);
		Set<Personaddress> personAddresses = new HashSet<Personaddress>();
		personAddresses.add(personAddress);
		person.setPersonaddresses(personAddresses);

		person = personService.savePersonDetails(person);
		return person;
	}

	public Workbook createWorkbook(File file) throws BiffException, IOException {
		return Workbook.getWorkbook(file);
	}

}
